package dao;

import bean.PageBean;
import bean.UserInfo;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import util.Connect;
import util.JdbcUtils;
import util.StringUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * The type User info dao.
 *
 * @author fzh
 */
public class UserInfoDao {
    private DeptMajorDao deptMajorDao = new DeptMajorDao();

    /**
     * 获取所有管理员未处理的身份验证申请信息
     *
     * @param pageBean the page bean
     * @return all user info
     * @throws Exception the exception
     * @author 邓晴予
     */
    public JSONArray getAllUserInfo(PageBean pageBean) throws Exception {
        StringBuilder sb = new StringBuilder("select ui.userInfoId userInfoId , userName, ui.userId userId, ui.name , phone, school, ui.stuId stuId, ui.major from userInfo as ui, dept_major major, dept_major dept, user where major.parentId=dept.id and ui.userId=user.userId and ui.major=major.id and user.userInfoId is null");
        if (pageBean != null) {
            sb.append(" limit ").append(pageBean.getStart()).append(",").append(pageBean.getRows());
        }
        List<Map<String, String>> list = JdbcUtils.queryForMap(sb.toString(), null);
        JSONArray jsonArray = new JSONArray();
        for (Map<String, String> map : list) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("userInfoId", map.get("userinfoid"));
            jsonObject.put("username", map.get("username"));
            jsonObject.put("userId", map.get("userid"));
            jsonObject.put("name", map.get("name"));
            jsonObject.put("phone", map.get("phone"));
            jsonObject.put("school", map.get("school"));
            jsonObject.put("major", deptMajorDao.getMajorById(map.get("major")));
            jsonObject.put("department", deptMajorDao.getDeptByMajorId(map.get("major")));
            jsonObject.put("stuId", map.get("stuid"));
            jsonArray.add(jsonObject);
        }
        return jsonArray;
    }

    /**
     * 验证通过后，把userInfo表里的userId置为null
     *
     * @param userInfoId the user info id
     * @return boolean
     * @author 邓晴予
     */
    public boolean deleteUserId(String userInfoId) {
        String sql = "update userInfo set userId=null where userInfoId=?";
        List<Object> para = new ArrayList<>();
        para.add(userInfoId);
        int result = JdbcUtils.update(sql, para);
        return result == 1;
    }

    /**
     * 验证失败后，将该userInfoId删掉
     *
     * @param userInfoId the user info id
     * @return boolean
     */
    public boolean deleteUserInfo(String userInfoId) {
        String sql = "delete from userInfo where userInfoId=?";
        List<Object> para = new ArrayList<>();
        para.add(userInfoId);
        int result = JdbcUtils.update(sql, para);
        return result == 1;
    }

    /**
     * User info list list.
     *
     * @param pageBean the page bean
     * @param userInfo the user info
     * @return the list
     * @throws Exception the exception
     */
    public List<Map<String, String>> userInfoList(PageBean pageBean, UserInfo userInfo) throws Exception {
        StringBuilder sb = new StringBuilder("select * from userInfo u");

        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(userInfo.getUserInfoId())) {
            sb.append(" and u.userInfoId =? ");
            para.add(userInfo.getUserInfoId());
        }
        if (StringUtil.isNotEmpty(userInfo.getName())) {
            sb.append(" and u.name = ?");
            para.add(userInfo.getName());
        }
        if (StringUtil.isNotEmpty(userInfo.getSchool())) {
            sb.append(" and u.school =?");
            para.add(userInfo.getSchool());
        }
        if (StringUtil.isNotEmpty(userInfo.getMajor())) {
            sb.append(" and u.major =?");
            para.add(userInfo.getMajor());
        }
        if (StringUtil.isNotEmpty(userInfo.getStuId())) {
            sb.append(" and u.stuId =?");
            para.add(userInfo.getStuId());
        }
        if (StringUtil.isNotEmpty(userInfo.getUserId())) {
            sb.append(" and u.userId =?");
            para.add(userInfo.getStuId());
        }
        if (pageBean != null) {
            sb.append(" limit ").append(pageBean.getStart()).append(",").append(pageBean.getRows());
        }
        String sql = sb.toString().replaceFirst("and", "where");
        return JdbcUtils.queryForMap(sql, para);
    }

    /**
     * Insert user info boolean.
     *
     * @param info the info
     * @return the boolean
     * @throws SQLException the sql exception
     */
    public boolean insertUserInfo(UserInfo info) throws SQLException {
        if (!info.canInsert()) {
            return false;
        }
        String sql = "INSERT INTO userInfo(userInfoId, name, school, major, stuId, userId) VALUES (null, ?, ?, ?, ?, ?)";
        List<Object> para = new ArrayList<>();
        para.add(info.getName());
        para.add(info.getSchool());
        para.add(info.getMajor());
        para.add(info.getStuId());
        para.add(info.getUserId());
        return 1 == JdbcUtils.update(sql, para);
    }

    /**
     * Insert with id int.
     *
     * @param info the info
     * @return the int
     * @throws SQLException the sql exception
     */
    public int insertWithId(UserInfo info) throws SQLException {
        if (!info.canInsert()) {
            throw new RuntimeException("Lack the request fields.");
        }
        Connection conn = Connect.getInstance().getConnection();
        String sql = "INSERT INTO userInfo (name, school, major, stuId) VALUES (?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, info.getName());
        ps.setString(2, info.getSchool());
        ps.setString(3, info.getMajor());
        ps.setString(4, info.getStuId());
        ps.executeUpdate();
        ResultSet set = ps.getGeneratedKeys();
        if (set.next()) {
            return set.getInt(1);
        }
        return -1;
    }

    /**
     * Is audit boolean.
     *
     * @param userId userid
     * @return true if auditing
     */
    public boolean isAudit(String userId) {
        String sql = "SELECT count(*) AS total FROM userInfo WHERE userId = ?";
        Connection connection = Connect.getInstance().getConnection();
        PreparedStatement ps;
        ResultSet rs;
        boolean bool;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, userId);
            rs = ps.executeQuery();
            bool = false;
            if (rs.next()) {
                bool = rs.getInt("total") != 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        JdbcUtils.closeResultSet(rs);
        JdbcUtils.closeStatement(ps);
        JdbcUtils.closeConnection(connection);
        return bool;
    }
}

